package com.example.mall.dao;

import com.example.mall.model.CommodityInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class CommodityInfoDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void insert(CommodityInfo commodityInfo) {
        String sql = "insert into commodityInfo (name, description, image, price, delivery, sellerID, remain) values (?, ?, ?, ?, ?, ?, ?)";
        jdbcTemplate.update(sql, commodityInfo.getName(), commodityInfo.getDescription(), commodityInfo.getImage(), commodityInfo.getPrice(), commodityInfo.getDelivery(), commodityInfo.getSellerID(), commodityInfo.getRemain());
    }

    public void delete(Integer id) {
        String sql = "delete from commodityInfo where id = ?";
        jdbcTemplate.update(sql, id);
    }

    public int updateDescriptionById(CommodityInfo commodityInfo) {
        String sql = "update commodityInfo set description = ? where id = ?";
        return jdbcTemplate.update(sql, commodityInfo.getDescription(), commodityInfo.getId());
    }

    public int updatePriceById(CommodityInfo commodityInfo) {
        String sql = "update commodityInfo set price = ? where id = ?";
        return jdbcTemplate.update(sql, commodityInfo.getPrice(), commodityInfo.getId());
    }

    public int updateDeliveryById(CommodityInfo commodityInfo) {
        String sql = "update commodityInfo set delivery = ? where id = ?";
        return jdbcTemplate.update(sql, commodityInfo.getDelivery(), commodityInfo.getId());
    }

    public int updateRemainById(CommodityInfo commodityInfo) {
        String sql = "update commodityInfo set remain = ? where id = ?";
        return jdbcTemplate.update(sql, commodityInfo.getRemain(), commodityInfo.getId());
    }

    public int updateShelves(CommodityInfo commodityInfo) {
        String sql = "update commodityInfo set shelves = ? where id = ?";
        return jdbcTemplate.update(sql, commodityInfo.getShelves(), commodityInfo.getId());
    }

    public CommodityInfo getById(Integer id) {
        String sql = "select * from commodityInfo where id = ? lock in share mode";
        RowMapper<CommodityInfo> rowMapper = new BeanPropertyRowMapper<>(CommodityInfo.class);
        CommodityInfo commodityInfo = null;
        try {
            commodityInfo = jdbcTemplate.queryForObject(sql, rowMapper, id);
        }
        catch (Exception e) {
            return commodityInfo;
        }
        return commodityInfo;
    }

    public CommodityInfo getBySIdName(Integer sellerID, String name) {
        String sql = "select * from commodityInfo where sellerID = ? and name = ? lock in share mode";
        RowMapper<CommodityInfo> rowMapper = new BeanPropertyRowMapper<>(CommodityInfo.class);
        CommodityInfo commodityInfo = null;
        try {
            commodityInfo = jdbcTemplate.queryForObject(sql, rowMapper, sellerID, name);
        }
        catch (Exception e) {
            return commodityInfo;
        }
        return commodityInfo;
    }

    public List<CommodityInfo> getBySId(Integer sellerID) {
        String sql = "select * from commodityInfo where sellerID = ? lock in share mode";
        RowMapper<CommodityInfo> rowMapper = new BeanPropertyRowMapper<>(CommodityInfo.class);
        return jdbcTemplate.query(sql, rowMapper, sellerID);
    }

    public CommodityInfo getByCSId(Integer cID, Integer sellerID) {
        String sql = "select * from commodityInfo where id = ? and sellerID = ? lock in share mode";
        RowMapper<CommodityInfo> rowMapper = new BeanPropertyRowMapper<>(CommodityInfo.class);
        CommodityInfo commodityInfo = null;
        try {
            commodityInfo = jdbcTemplate.queryForObject(sql, rowMapper, cID, sellerID);
        }
        catch (Exception e) {
            return commodityInfo;
        }
        return commodityInfo;
    }

    public List<CommodityInfo> get(Integer sellerID, String cID, String shelves, String tagName, String name) {
        String sql = "select distinct id, name, description, image, price, delivery, sellerID, remain, author, shelves from commodityInfo " +
                "natural join ((select cID as id, tID from belong) as T1) natural join ((select id as tID, name as tagName from tag)as T2)" +
                "where sellerID = ? and id like ? and shelves like ? and tagName like ? and name like ? ";
        RowMapper<CommodityInfo> rowMapper = new BeanPropertyRowMapper<>(CommodityInfo.class);
        return jdbcTemplate.query(sql, rowMapper, sellerID, cID, shelves, tagName, name);
    }

}
